
package com.qingcheng.controller.order;

import com.alibaba.dubbo.config.annotation.Reference;
import com.qingcheng.pojo.order.Order;
import com.qingcheng.service.order.OrderService;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;


@RestController
@RequestMapping("/excel")
public class Excel {
    @Reference
    private OrderService orderService;
    @GetMapping("/test")
    public void loadExcel(HttpServletResponse response) throws Exception {

        Date date=new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd,HH-mm-ss");
        String filename = sdf.format(date)+".xlsx";

        List<Order> orders = orderService.findAll();
        //String [] title ={"id","total_num","total_money","pre_money","post_fee","pay_money","pay_type","create_time","update_time","pay_time","consign_time","end_time","close_time","shipping_name","shipping_code","username","buyer_message","buyer_rate","receiver_contact","receiver_mobile","receiver_address","source_type","transaction_id","order_status","pay_status","consign_status","is_delete"};
        String [] title ={"订单","数量合计","金额合计","优惠金额","邮费","实付金额","支付类型","创建时间","更新时间","付款时间","发货时间","交易完成时间","关闭时间","物流名称","物流单号","用户名称","买家留言","是否评价","收货人","手机","地址","订单来源","流水号","订单状态","支付状态","发货状态","是否删除"};
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell =null;
        XSSFCellStyle setBorder = workbook.createCellStyle();
        setBorder.setFillForegroundColor((short)1);// 设置背景色
        //setBorder.setFillForegroundColor(HSSFColor.GREEN.index);
        setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

        XSSFFont font = workbook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 16);//设置字体大小

        XSSFFont font2 = workbook.createFont();
        font2.setFontName("仿宋_GB2312");
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        font2.setFontHeightInPoints((short) 12);

        setBorder.setFont(font);//选择需要用到的字体格式
        sheet.setColumnWidth(0, 8000);
        for (int i = 1; i <27 ; i++) {
            sheet.setColumnWidth(i,4000);
        }



        for (int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(setBorder);
        }
        for (int i = 1; i < orders.size(); i++) {
            Order order = orders.get(i);
            if (order == null) {
                continue;
            }
            XSSFRow row1 = sheet.createRow(i);
            XSSFCell cell2 = row1.createCell(0);
            cell2.setCellValue(order.getId()+"");
            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(1);
            if(order.getTotalNum()==null)
            { cell2.setCellValue("");}else{
                cell2.setCellValue(order.getTotalNum()+"");}
            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(2);
            if(order.getTotalMoney()==null)
            { cell2.setCellValue("");}else{
                cell2.setCellValue(order.getTotalMoney()+"");}

            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(3);
            if(order.getPreMoney()==null)
            { cell2.setCellValue("");}else{
                cell2.setCellValue(order.getPreMoney()+"");}

            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(4);
            if(order.getPostFee()==null)
            { cell2.setCellValue("");}else{
                cell2.setCellValue(order.getPostFee()+"");}

            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(5);
            if(order.getPayMoney()==null)
            { cell2.setCellValue("");}else{
                cell2.setCellValue(order.getPayMoney()+"");}

            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(6);
            if(order.getPayType()==null)
            { cell2.setCellValue("");}else{
                cell2.setCellValue(order.getPayType()+"");}

            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(7);
            if(order.getCreateTime()==null)
            { cell2.setCellValue("");}else{
                cell2.setCellValue( sdf.format(order.getCreateTime())+"");}

            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(8);
            if(order.getUpdateTime()==null)
            { cell2.setCellValue("");}else{
                cell2.setCellValue(sdf.format(order.getUpdateTime())+"");}

            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(9);
            if(order.getPayTime()==null)
            { cell2.setCellValue("");}else{
                cell2.setCellValue(sdf.format(order.getPayTime())+"");}

            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(10);
            if(order.getConsignTime()==null)
            { cell2.setCellValue("");}else{
                cell2.setCellValue(sdf.format(order.getConsignTime())+"");}

            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(11);
            if(order.getEndTime()==null)
            { cell2.setCellValue("");}else{
                cell2.setCellValue(sdf.format(order.getEndTime())+"");}

            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(12);
            if(order.getCloseTime()==null)
            { cell2.setCellValue("");}else{
                cell2.setCellValue(sdf.format(order.getCloseTime())+"");}

            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(13);
            cell2.setCellValue(order.getShippingName());
            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(14);
            cell2.setCellValue(order.getShippingCode());
            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(15);
            cell2.setCellValue(order.getUsername());
            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(16);
            cell2.setCellValue(order.getBuyerMessage());
            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(17);
            cell2.setCellValue(order.getBuyerRate());
            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(18);
            cell2.setCellValue(order.getReceiverContact());
            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(19);
            cell2.setCellValue(order.getReceiverMobile());
            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(20);
            cell2.setCellValue(order.getReceiverAddress());
            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(21);
            cell2.setCellValue(order.getSourceType());
            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(22);
            cell2.setCellValue(order.getTransactionId());
            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(23);
            cell2.setCellValue(order.getOrderStatus());
            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(24);
            cell2.setCellValue(order.getPayStatus());
            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(25);
            cell2.setCellValue(order.getConsignStatus());
            cell2.setCellStyle(setBorder);
            cell2=row1.createCell(26);
            cell2.setCellValue(order.getIsDelete());
            cell2.setCellStyle(setBorder);

        }


                response.setContentType("application/octet-stream;;charset=UTF-8");
                response.setHeader("Content-disposition", "attachment;filename="+filename );
                OutputStream outputStream = response.getOutputStream();

                response.flushBuffer();
                workbook.write(outputStream);
                outputStream.flush();
                outputStream.close();






        }

}
